package com.sffzh.test;

public class OBUtestSql {
	public String startTime,endTime;
	
	public static void main(String[] args) {
		OBUtestSql o=new OBUtestSql();
		o.startTime="2015-12-28 10:04:12";
		o.endTime="2015-12-29 09:04:12";
		System.out.println(o.getSql());
		
	}
	public String getSql(){
		//").append(startTime).append("==").append(startTime).append("
		//$endTime$=='").append(endTime).append("'
		StringBuilder b=new StringBuilder();
		b.append("SELECT e.vehPlateLicenceNumber as vid").append("\n");
		b.append(",e.passTime as passTime").append("\n");
		b.append(",e.oBUId as oBUId").append("\n");
		b.append(",1 as type").append("\n");
		b.append("from tbl_vehicleentry e").append("\n");
		b.append("WHERE e.passTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and e.passTime<='").append(endTime).append("'").append("\n");
		b.append("and e.oBUId != ''").append("\n");
		b.append("UNION").append("\n");
		b.append("(").append("\n");
		b.append("SELECT ex.vehPlateLicenceNumber,ex.exitTime as passTime").append("\n");
		b.append(",ex.oBUId").append("\n");
		b.append(",2 as type").append("\n");
		b.append("from tbl_vehicleexit ex").append("\n");
		b.append("where ex.exitTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and ex.exitTime<='").append(endTime).append("'").append("\n");
		b.append("and ex.oBUId != ''").append("\n");
		b.append(")").append("\n");
		b.append("UNION(").append("\n");
		b.append("SELECT i.vehPlateLicenceNumber,i.optTime as passTime").append("\n");
		b.append(",i.obuId").append("\n");
		b.append(",i.laneType as type").append("\n");
		b.append("from tbl_vehicleintercept i").append("\n");
		b.append("where i.optTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and i.optTime<='").append(endTime).append("'").append("\n");
		b.append("and i.obuId != ''").append("\n");
		b.append(")").append("\n");
		b.append("ORDER BY passTime").append("\n");
		b.append(";").append("\n");
		b.append("\n");
		b.append("\n");
		b.append("\n");
		b.append("(").append("\n");
		b.append("SELECT e.vehPlateLicenceNumber as vid").append("\n");
		b.append(",e.passTime as passTime").append("\n");
		b.append(",e2.exitTime").append("\n");
		b.append(",e.oBUId as oBUId").append("\n");
		b.append(",e.laneId as type").append("\n");
		b.append(",'入场' as t").append("\n");
		b.append("#,abs(timediff(e.passTime,e2.exitTime)) abs").append("\n");
		b.append("from tbl_vehicleentry e , tbl_vehicleexit  e2").append("\n");
		b.append("WHERE e.passTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
	b.append("and e.passTime<='").append(endTime).append("'").append("\n");
		b.append("and e.oBUId != ''").append("\n");
		b.append("and e.oBUId=e2.oBUId").append("\n");
		b.append("and (timediff(e.passTime,e2.exitTime))<'00:00:30'").append("\n");
		b.append("and (timediff(e.passTime,e2.exitTime))>=0").append("\n");
		b.append(")").append("\n");
		b.append("UNION").append("\n");
		b.append("(").append("\n");
		b.append("SELECT ex.vehPlateLicenceNumber,ex.exitTime as passTime").append("\n");
		b.append(",ex2.passTime").append("\n");
		b.append(",ex.oBUId").append("\n");
		b.append(",2 as type").append("\n");
		b.append(",'出场' as t").append("\n");
		b.append("from tbl_vehicleexit ex ,tbl_vehicleentry ex2").append("\n");
		b.append("where ex.exitTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and ex.exitTime<='").append(endTime).append("'").append("\n");
		b.append("and ex.oBUId != ''").append("\n");
		b.append("\n");
		b.append("and ex.oBUId=ex2.oBUId").append("\n");
		b.append("and (timediff(ex.exitTime,ex2.passTime))<'00:00:30'").append("\n");
		b.append("and (timediff(ex.exitTime,ex2.passTime))>0").append("\n");
		b.append(")").append("\n");
		b.append("UNION").append("\n");
		b.append("(").append("\n");
		b.append("SELECT").append("\n");
		b.append("DISTINCT").append("\n");
		b.append(" i.vehPlateLicenceNumber,i.optTime as passTime").append("\n");
		b.append(",i2.optTime").append("\n");
		b.append(",i.obuId").append("\n");
		b.append(",i.laneType as type").append("\n");
		b.append(",'拦截入场' as t").append("\n");
		b.append("from tbl_vehicleintercept i , tbl_vehicleintercept i2").append("\n");
		b.append("where i.optTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and i.optTime<='").append(endTime).append("'").append("\n");
		b.append("and i.laneType=1").append("\n");
		b.append("and i.obuId != ''").append("\n");
		b.append("and i.obuId=i2.obuId and i.id!=i2.id").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))<'00:00:30'").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))>=0").append("\n");
		b.append(")").append("\n");
		b.append("UNION").append("\n");
		b.append("(").append("\n");
		b.append("SELECT").append("\n");
		b.append("DISTINCT").append("\n");
		b.append(" i.vehPlateLicenceNumber,i.optTime as passTime").append("\n");
		b.append(",i2.optTime").append("\n");
		b.append(",i.obuId").append("\n");
		b.append(",i.laneType as type").append("\n");
		b.append(",'拦截出场' as t").append("\n");
		b.append("from tbl_vehicleintercept i , tbl_vehicleintercept i2").append("\n");
		b.append("where i.optTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and i.optTime<='").append(endTime).append("'").append("\n");
		b.append("and i.obuId != ''").append("\n");
		b.append("and i.laneType=2").append("\n");
		b.append("and i.obuId=i2.obuId and i.id!=i2.id").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))<'00:00:30'").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))>0").append("\n");
		b.append(")").append("\n");
		b.append("UNION").append("\n");
		b.append("(").append("\n");
		b.append("SELECT").append("\n");
		b.append("DISTINCT").append("\n");
		b.append(" i.vehPlateLicenceNumber,i.optTime as passTime").append("\n");
		b.append(",i2.optTime").append("\n");
		b.append(",i.obuId").append("\n");
		b.append(",i.laneType as type").append("\n");
		b.append(",'拦截' as t").append("\n");
		b.append("from tbl_vehicleintercept i , tbl_vehicleintercept i2").append("\n");
		b.append("where i.optTime>'").append(startTime).append("'").append("\n");
		if(endTime!=null&&!"".equals(endTime))
		b.append("and i.optTime<='").append(endTime).append("'").append("\n");
		b.append("and i.obuId != ''").append("\n");
		b.append("and i.laneType!=2").append("\n");
		b.append("and i.laneType!=1").append("\n");
		b.append("and i.obuId=i2.obuId and i.id!=i2.id").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))<'00:00:30'").append("\n");
		b.append("and (timediff(i.optTime,i2.optTime))>=0").append("\n");
		b.append(")\n");
		b.append("\n");
		b.append("ORDER BY passTime").append("\n");
		b.append(";").append("\n");
		return b.toString();
	}
	

}
